

*This file compiles the Chicago case-level crime data and merges with Census Tract-level information on poverty and store locations.

********************************************************************************
***** Prepping panels by day and day by tract

forvalues y = 2007/2013{
clear
set more off
set mem 4000m

insheet using "crimes_stores_`y'.txt"

*place dummies
g grocery = (locationde == "GROCERY FOOD STORE")
g atm = (locationde == "ATM (AUTOMATIC TELLER MACHINE)")
g bank = (locationde == "BANK" | locationde == "CREDIT UNION" | locationde == "SAVINGS AND LOAN")
g all_atms = (grocery == 1 | atm == 1 | bank == 1 |  locationde == "CONVENIENCE STORE" |  locationde == "DRUG STORE" | locationde == "GAS STATION")
g cha = (locationde == "CHA APARTMENT" | locationde == "CHA BREEZEWAY" | locationde == "CHA ELEVATOR" | /*
*/	locationde == "CHA GROUNDS" | locationde == "CHA HALLWAY" | locationde == "CHA HALLWAY/STAIRWELL/ELEVATOR" | /*
*/	locationde == "CHA LOBBY" | locationde == "CHA PARKING LOT" | locationde == "CHA PARKING LOT/GROUNDS" | /*
*/	locationde == "CHA PLAY LOT" | locationde == "CHA STAIRWELL")


*Based on crow flies distance
g snap_loc_1b = distance <661
g snap_loc_2b = distance <1321


*crime dummies
g theft = (primarytyp == "THEFT")
g robbery = (primarytyp == "ROBBERY")
g assault = (primarytyp == "ASSAULT" | primarytyp == "BATTERY")

g drug_use = (descriptio == "ATTEMPT POSSESSION CANNABIS" | descriptio == "ATTEMPT POSSESSION NARCOTICS" | descriptio == "POS: HYPODERMIC NEEDLE" | descriptio == "POSS: AMPHETAMINES"  /*
*/	| descriptio == "POSS: BARBITUATES" | descriptio == "POSS: CANNABIS 30GMS OR LESS" | descriptio == "POSS: CANNABIS MORE THAN 30GMS" | descriptio == "POSS: COCAINE" | descriptio == "POSS: CRACK"  /*
*/	| descriptio == "POSS: HALLUCINOGENS" | descriptio == "POSS: HEROIN(BLACK TAR)" | descriptio == "POSS: HEROIN(BRN/TAN)" | descriptio == "POSS: HEROIN(WHITE)" | descriptio == "POSS: LOOK-ALIKE DRUGS"  /*
*/	| descriptio == "POSS: METHAMPHETAMINES" | descriptio == "POSS: PCP" | descriptio == "POSS: SYNTHETIC DRUGS" | descriptio == "POSSESSION OF DRUG EQUIPMENT" | descriptio == "POSSESSION: SYNTHETIC MARIJUANA")
	
g drug_sell = (descriptio == "DEL CONT SUBS TO PERSON <18" | descriptio == "DELIVER CANNABIS TO PERSON <18" | descriptio == "MANU/DEL:CANNABIS 10GM OR LESS" | descriptio == "MANU/DEL:CANNABIS OVER 10 GMS" /*
*/	| descriptio == "MANU/DELIVER: HALLUCINOGEN" | descriptio == "MANU/DELIVER: HEROIN (WHITE)" | descriptio == "MANU/DELIVER: HEROIN(BRN/TAN)" | descriptio == "MANU/DELIVER: METHAMPHETAMINES" /*
*/	| descriptio == "MANU/DELIVER:AMPHETAMINES" | descriptio == "MANU/DELIVER:BARBITUATES" | descriptio == "MANU/DELIVER:COCAINE" | descriptio == "MANU/DELIVER:CRACK" | descriptio == "MANU/DELIVER:HEROIN(BLACK TAR)" /*
*/	| descriptio == "MANU/DELIVER:LOOK-ALIKE DRUG" | descriptio == "MANU/DELIVER:PCP" | descriptio == "MANU/DELIVER:SYNTHETIC DRUGS" | descriptio == "MANU/POSS. W/INTENT TO DELIVER: SYNTH.." /*
*/	| descriptio == "SALE/DEL DRUG PARAPHERNALIA" | descriptio == "SALE/DEL HYPODERMIC NEEDLE" | descriptio == "SOLICIT NARCOTICS ON PUBLICWAY")
	
g financial_gain = (primarytyp == "BURGLARY" | primarytyp == "MOTOR VEHICLE THEFT"  | drug_sell == 1 | primarytyp == "PROSTITUTION" | primarytyp == "ROBBERY" | primarytyp == "THEFT")
	
g cash_card = (descriptio == "ILLEGAL USE CASH CARD") 


rename domestic home
g domestic = (home == "true")

g drugs = drug_sell + drug_use
	
*place and crime dummies
g theft_grocery = (grocery == 1 & theft ==1)
g drugs_cha = (drugs == 1 & cha == 1)

g any = 1

collapse (sum) any grocery atm bank all_atms cha theft robbery assault drugs cash_card domestic drug_sell drug_use financial_gain theft_grocery drugs_cha snap_loc_1b snap_loc_2b, by(date_m geo_id)

save tract_panel_`y'.dta, replace

collapse (sum) any grocery atm bank all_atms cha theft robbery assault drugs cash_card domestic drug_sell drug_use financial_gain theft_grocery drugs_cha snap_loc_1b snap_loc_2b, by(date_m)

save day_panel_`y'.dta, replace

}

forvalues y = 2007/2012{
append using day_panel_`y'.dta
}

save day_panel_07_13.dta, replace

clear
forvalues y = 2007/2013{
append using tract_panel_`y'.dta
}

g censustract=substr(geo_id,10,.)
destring censustract, g(censustract2)
xtset censustract2 date_m
tsfill, full
tostring censustract2, gen(censustractS) format("%12.0f")
replace geo_id="1400000US"+censustractS if geo_id==""
drop censustract2 censustractS 

save tract_panel_07_13.dta, replace

********************************************************************************
******** Cleaning Census Tract poverty and stores data
clear
insheet using "tract_stores_2010.txt"
keep geo_id count_
rename count_ stores_count
drop if stores_count == 0
save stores_2010_tr.dta, replace

*using 2010 ACS data on household SNAP participation
clear
insheet using "ACS_10.csv"

g per_snap = snap_households/total_households
	replace per_snap = 0 if per_snap == .
save snap_2010_tr.dta, replace	
outsheet using snap_2010_tr.csv, replace
g geo_id_short = substr(geo_id, 10,.)
drop geo_id
outsheet using snap_2010_tr_cook.csv, replace


**merging SNAP participation and food desert info from USDA
use tract_panel_07_13.dta, clear
keep geo_id
duplicates drop
drop if geo_id == ""

merge m:1 geo_id using snap_2010_tr
keep if _merge == 3
drop _merge

merge m:1 geo_id using stores_2010_tr
drop if _merge == 2
replace stores_count = 0 if _merge == 1
drop _merge

merge m:1 geo_id using fooddesert_tr.dta
drop if _merge == 2
drop _merge
save stores_snap_daypanel.dta, replace

use tract_panel_07_13.dta, clear
duplicates drop
drop if geo_id == ""
merge m:1 geo_id using stores_snap_daypanel.dta
drop if _merge==1

foreach x in any grocery bank cha theft robbery assault drugs cash_card domestic drug_sell drug_use financial_gain drugs_cha {
replace `x'=0 if `x'==.
}
drop if geo_id==""
drop _merge


*creating date info
g day = day(date_m)
g dow = dow(date_m)
	* 0 = Sunday
g month = month(date_m)
g year = year(date_m)
	
g pre = ((year < 2010)| (year == 2010 & month < 2))

drop if (year == 2013 & month >6) | (year >2013) | year < 2007

*merging data for supermarket subgroups
merge m:1 censustract year using "supermarkets0713.dta"
replace supermarkets=0 if supermarkets==.
drop _merge 

**merging weather data for controls
merge m:1 day month year using "weather_panel.dta"
drop _merge

*merging SNAP redemptions data to test smoothness
merge m:1 date_m using "SNAP_reimburse.dta", force
drop _merge

drop if geo_id==""

save data_IL.dta, replace

